package getFundInfo;

import initDB.DBConnection;

import java.io.FileWriter;
import java.sql.*;
import java.util.HashMap;

import getFundInfo.GetFundInfo;
import net.sf.json.JSONArray;

public class InsertFundData {

	static String driverName = "org.h2.Driver";
	static String url = "jdbc:h2:E:/commonProgram/hotchpotch/crawler/database/dbFile";
	static String userName = "sa";
	static String password = "";
	static int count = 0;
	static int batchSize = 10000;
	static Connection conn = null;
	public static String[] codes = { "164402", "590002", "000928", "001409", "000534", "161610", "001195", "398001",
			"202002", "481001" };
	public static HashMap<String, String> codeMap = new HashMap<String, String>() {
		private static final long serialVersionUID = 1L;
		{
			put("164402", "1");
			put("590002", "0.6,0.61");
			put("000928", "0.6");
			put("001409", "0.6");
			put("000534", "0.6");
			put("161610", "0.6");
			put("001195", "0.6");
			put("398001", "0.6");
			put("202002", "0.6");
			put("481001", "0.6");
		}
	};

	public static void createTable() throws Exception {
		ResultSet rs = null;
		DatabaseMetaData dMetaData = conn.getMetaData();
		rs = dMetaData.getTables(null, null, "NET_ASSERT_VALUE", null);
		if(!rs.next()){
			System.out.println("创建表NET_ASSERT_VALUE");
			conn.createStatement().executeUpdate("CREATE TABLE net_assert_value(ID VARCHAR(64) NOT NULL,FUND_DATE CHAR(10) DEFAULT NULL,FUND_CODE CHAR(6) DEFAULT NULL,NET_ASSERT_VALUE DOUBLE DEFAULT NULL,PRIMARY KEY (ID))");
		}else{
			System.out.println("已存在表NET_ASSERT_VALUE");
		}
		rs = dMetaData.getTables(null, null, "FUND_INFO", null);
		if(!rs.next()){
			System.out.println("创建表FUND_INFO");
			conn.createStatement().executeUpdate("CREATE TABLE fund_info (ID CHAR(64),FUND_CODE CHAR(6),FUND_NAME VARCHAR(20),FUND_TYPE CHAR(10),FUND_SHORT_NAME CHAR(20),PRIMARY KEY (ID))");
		}else{
			System.out.println("已存在表FUND_INFO");
		}
	}
	
	public static void insertNetAssertValueIncrement(String[]codes)  {
		int fundCount = 0;
		JSONArray jsonArray = null;
		for (String c : codes) {
			ResultSet rs = null;
			try {
				rs = conn.createStatement().executeQuery("select count(*) from NET_ASSERT_VALUE a where a.fund_code ='" + c+"'");
				rs.next();
				fundCount = rs.getInt(1);
			} catch (SQLException e) {
				e.printStackTrace();
			}
			//System.out.println("fundCount" + fundCount);
			
			String s = GetFundInfo.danWeiJingZhi("http://fund.eastmoney.com/pingzhongdata/" + c + ".js");
			if(s == "") continue;
			jsonArray = JSONArray.fromObject(s);
			//System.out.println("jsonArray.size()" + jsonArray.size());
			JSONArray tmp = null;
			if (jsonArray.size() > fundCount) {
				//System.out.println("开始增量插入");
				String sql = "insert into NET_ASSERT_VALUE(id,fund_code,fund_date,net_assert_value) values(?,'" + c
						+ "',?,?)";
				PreparedStatement ps = null;
				try {
					ps = conn.prepareStatement(sql);
					for (int i = fundCount; i < jsonArray.size(); i++) {
						tmp = jsonArray.getJSONArray(i);
						ps.setString(1, String.valueOf(count++));
						ps.setString(2, tmp.get(0).toString());
						ps.setDouble(3, Double.valueOf(tmp.get(1).toString()));
						ps.addBatch();
						if (i > 0 && (i % batchSize == 0)) {
							ps.executeBatch();
						}
					}
					ps.executeBatch();
				} catch (SQLException e) {
					count--;
					e.printStackTrace();
				}
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}

	}

	/**
	 * 先删除fund_info记录然后插入数据
	 * @throws Exception
	 */
	public static void insertFundInfo() throws Exception {
		conn.createStatement().executeUpdate("TRUNCATE TABLE fund_info");
		System.out.println("删除表fund_info成功");
		count = getCount("select count(*) as count from fund_info");
		System.out.println("总数:count:" + count);
		String funds = GetJson.getResponse("http://fund.eastmoney.com/js/fundcode_search.js");
		funds = funds.substring(funds.indexOf("["), funds.lastIndexOf(";"));
		try {

			JSONArray jsonArray = JSONArray.fromObject(funds);
			PreparedStatement ps = null;
			try {
				JSONArray tmp = null;
				String sql = "insert into fund_info (id, fund_code, fund_short_name, fund_name,  fund_type) values(?, ?, ?,?,?)";
				ps = conn.prepareStatement(sql);
				for (int i = 0; i < jsonArray.size(); i++) {
					tmp = jsonArray.getJSONArray(i);
					ps.setString(1, String.valueOf(count++));
					ps.setString(2, tmp.getString(0));
					ps.setString(3, tmp.getString(1));
					ps.setString(4, tmp.getString(2));
					ps.setString(5, tmp.getString(3));
					ps.addBatch();
				}
				ps.executeBatch();
			} catch (Exception e) {
				System.out.println("插入数据失败!");
				ps.close();
				conn.close();
			}

		} catch (Exception e) {
			System.out.println("JSON转换失败!");
			e.printStackTrace();
		}

	}

	public static void truncateTable(String tableName) throws Exception {
		conn.createStatement().executeUpdate("TRUNCATE TABLE " + tableName);
	}
	
	public static void dropTable(String tableName) throws Exception {
		conn.createStatement().executeUpdate("DROP TABLE " + tableName);
	}

	public static String[] getOnlineFundCode() {
		String fundCode = GetJson.getResponse("http://fund.eastmoney.com/js/fundcode_search.js");
		fundCode = fundCode.substring(fundCode.indexOf("["), fundCode.lastIndexOf(";"));
		JSONArray jsonArray = JSONArray.fromObject(fundCode);
		String codes[] = new String[jsonArray.size()];
		for (int i = 0; i < jsonArray.size(); i++) {
			codes[i] = jsonArray.getJSONArray(i).getString(0);
		}
		return codes;
	}
	
	public static String[] getDatabaseFundCode() throws SQLException {
		ResultSet rs = conn.createStatement().executeQuery("SELECT distinct(a.FUND_CODE) FROM net_assert_value a");
		int count = getCount("select count(distinct(a.FUND_CODE)) as count from net_assert_value a");
		String codes[] = new String[count];
		int i = 0;
		while (rs.next()) {
			codes[i++] = rs.getString("FUND_CODE");
		}
		return codes;
	}

	public static int getCount(String sql) {
		ResultSet rs = null;
		int count = 0;
		try {
			rs = conn.createStatement().executeQuery(sql);
			if (rs.next()) {
				count = rs.getInt("count");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return count;
	}

	public static void insertNetAssertValue(String codes[]) throws Exception {
		FileWriter fw = new FileWriter("c.txt");
		fw.write(new java.util.Date().toString() + "\n");
		PreparedStatement ps = null;
		for (String c : codes) {
			try {
				JSONArray jsonArray = JSONArray.fromObject(GetFundInfo.danWeiJingZhi("http://fund.eastmoney.com/pingzhongdata/" + c + ".js"));
				JSONArray tmp = null;
				String sql = "insert into NET_ASSERT_VALUE(id,fund_code,fund_date,net_assert_value) values(?,'" + c + "',?,?)";
				ps = conn.prepareStatement(sql);
				for (int i = 0; i < jsonArray.size(); i++) {
					tmp = jsonArray.getJSONArray(i);
					ps.setString(1, String.valueOf(count++));
					ps.setString(2, tmp.get(0).toString());
					ps.setDouble(3, Double.valueOf(tmp.get(1).toString()));
					ps.addBatch();
					if (i > 0 && (i % batchSize == 0)) {
						ps.executeBatch();
					}
				}
				ps.executeBatch();
			} catch (Exception e) {
				count--;
				fw.write(c + "\n");
			}
			ps.close();
		}
		fw.write(new java.util.Date().toString());
		fw.flush();
		fw.close();
	}

	public static void main(String[] a) throws Exception {
		conn = DBConnection.getConnection("org.h2.Driver","jdbc:h2:E:/commonProgram/hotchpotch/crawler/database/dbFile","sa", "");
		conn = DBConnection.getConnection("com.mysql.jdbc.Driver","jdbc:mysql://192.168.50.128:3306/eastmoney?useUnicode=true&characterEncoding=utf8","root", "123456");
		
		/*dropTable("NET_ASSERT_VALUE");
		dropTable("FUND_INFO");*/
		createTable();
		
		System.out.println("FUND_INFO->"+getCount("select count(*) as count from FUND_INFO"));
		System.out.println("NET_ASSERT_VALUE->"+getCount("select count(*) as count from NET_ASSERT_VALUE"));
		System.out.println(count);
		count = getCount("select count(*) as count from NET_ASSERT_VALUE");
		//insertFundInfo();
		
		//insertNetAssertValue(codes);
		
		//insertNetAssertValueIncrement(getOnlineFundCode());
		insertNetAssertValueIncrement(getDatabaseFundCode());
		//String cs[] = {"000004"};
		//insertNetAssertValueIncrement(cs);

		System.out.println("终于到最后了"+count);
		conn.close();
	}
}
